﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using BTN04.DTO;

namespace BTN04.DAO
{

    public class NguoiDungDAO
    {
        public int KiemtraTenDangNhapDaTonTaiHayChua(string tendangnhap)
        {
            
            string sql_String = "Select MaNguoiDung from NguoiDung where TenDangNhap='" + tendangnhap +"'";
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            connect.Open();

            SqlCommand comnd = new SqlCommand(sql_String, connect);

            SqlDataReader rd = comnd.ExecuteReader();
            int MaNguoiDung = 0;
            while (rd.Read())
            {                           
                MaNguoiDung = Int32.Parse(rd["MaNguoiDung"].ToString());               
                
            }
            connect.Close();

            return MaNguoiDung;
        }
        public int Dangkynguoidung(NguoiDungDTO nd)
        {
            string sql_string =string.Format("Insert into NguoiDung(TenDangNhap,MatKhau,HoTen,Email,DiaChi,NgaySinh,MaLoaiNguoiDung,MaBanNick,SoLanGoSaiMatKhau) values('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7},{8})",nd.TenDangNhap,nd.MatKhau,nd.HoTen,nd.Email,nd.DiaChi,nd.NgaySinh.ToShortDateString(),nd.MaLoaiNguoiDung,nd.MaBanNick,nd.SoLanGoSaiMatKhau);

            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            connect.Open();
            SqlCommand comnd = new SqlCommand(sql_string, connect);
            int kiem=comnd.ExecuteNonQuery();
            connect.Close();
            return kiem;
            
        }
        public NguoiDungDTO LayThongTinNguoiDungTheoTenDangNhap(string tendangnhap)
        {
            string sql_String = "Select * from NguoiDung where TenDangNhap='" + tendangnhap + "'";
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            connect.Open();

            SqlCommand comnd = new SqlCommand(sql_String, connect);

            SqlDataReader rd = comnd.ExecuteReader();
            NguoiDungDTO ndDTO = new NguoiDungDTO();
            while (rd.Read())
            {
                ndDTO.MaNguoiDung = int.Parse(rd["MaNguoiDung"].ToString());
                ndDTO.TenDangNhap = rd["TenDangNhap"].ToString();
                ndDTO.MatKhau = rd["MatKhau"].ToString();
                ndDTO.HoTen = rd["HoTen"].ToString();
                ndDTO.DiaChi = rd["DiaChi"].ToString();
                ndDTO.Email = rd["Email"].ToString();
                ndDTO.DienThoai = rd["DienThoai"].ToString();
                ndDTO.GioiTinh = rd["GioiTinh"].ToString();
                ndDTO.NgaySinh = DateTime.Parse(rd["NgaySinh"].ToString());
                ndDTO.SoLanGoSaiMatKhau = int.Parse(rd["SoLanGoSaiMatKhau"].ToString());
                ndDTO.MaLoaiNguoiDung = int.Parse(rd["MaLoaiNguoiDung"].ToString());
                try
                {
                    ndDTO.MaBanNick = int.Parse(rd["MaBanNick"].ToString());
                }
                catch
                {
                    ndDTO.MaBanNick = 0;
                }
            }
            connect.Close();
            return ndDTO;
        }
        //
        public List<NguoiDungDTO> LayThongDanhSachNguoiDung()
        {
            string sql_String = "Select * from NguoiDung Where MaLoaiNguoiDung !=1";
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            connect.Open();

            SqlCommand comnd = new SqlCommand(sql_String, connect);

            SqlDataReader rd = comnd.ExecuteReader();
            List<NguoiDungDTO> dsnd = new List<NguoiDungDTO>();
            while (rd.Read())
            {
                NguoiDungDTO ndDTO = new NguoiDungDTO();
                ndDTO.MaNguoiDung = int.Parse(rd["MaNguoiDung"].ToString());
                ndDTO.TenDangNhap = rd["TenDangNhap"].ToString();
                ndDTO.MatKhau = rd["MatKhau"].ToString();
                ndDTO.HoTen = rd["HoTen"].ToString();
                ndDTO.DiaChi = rd["DiaChi"].ToString();
                ndDTO.Email = rd["Email"].ToString();
                ndDTO.GioiTinh = rd["GioiTinh"].ToString();
                ndDTO.DienThoai = rd["DienThoai"].ToString();
                ndDTO.NgaySinh = Convert.ToDateTime(rd["NgaySinh"]);
                ndDTO.SoLanGoSaiMatKhau = int.Parse(rd["SoLanGoSaiMatKhau"].ToString());
                ndDTO.MaLoaiNguoiDung = int.Parse(rd["MaLoaiNguoiDung"].ToString());
                try
                {
                    ndDTO.MaBanNick = int.Parse(rd["MaBanNick"].ToString());
                }
                catch
                {
                    ndDTO.MaBanNick = 0;
                }
                dsnd.Add(ndDTO);
            }
            connect.Close();
            return dsnd;
        }
        public DataTable KiemTraDangNhap(string tendangnap, string matkhau)
        {
            DataTable dt = new DataTable();
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            //matkhau = DataProvider.Md5AddSecret(matkhau);
            string strSQL = "SELECT * FROM NGUOIDUNG WHERE TenDangNhap = '"+tendangnap +"' AND MatKhau='"+matkhau+"'";
            SqlCommand comand = new SqlCommand(strSQL, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            adapter.Fill(dt);
            return dt;
        }
        
        public DataTable LayThongTinNguoiDung(string tendangnhap)
        {
            DataTable dt = new DataTable();
            return dt;
        }
        public int UpdateMatKhau(string tendangnhap, string matkhau)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
            //matkhau = DataProvider.Md5AddSecret(matkhau);
            string strSQL = "UPDATE NguoiDung SET MatKhau = '" + matkhau +"'";
            strSQL += " WHERE TenDangNhap = '" + tendangnhap + "'";
            SqlCommand comand = new SqlCommand(strSQL, cnn);
            int kq;
            kq = Convert.ToInt32(comand.ExecuteScalar());
            return kq;
        }
        public int UpdateThongTinNguoiDung(NguoiDungDTO ngDTO)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            cnn.Open();
           /* string strSQL = "UPDATE NguoiDung SET HoTen=?,NgaySinh=?,Email=?,DiaChi=?,GioiTinh=?,DienThoai=?";
            strSQL += " WHERE TenDangNhap = '" + ngDTO.TenDangNhap + "'";
            SqlCommand comand = new SqlCommand(strSQL, cnn);
            SqlParameter para = new SqlParameter("@HoTen", SqlDbType.NVarChar);
            para.Value = ngDTO.HoTen;
            comand.Parameters.Add(para);
            para = new SqlParameter("@NgaySinh", SqlDbType.DateTime);
            para.Value = ngDTO.NgaySinh;
            comand.Parameters.Add(para);
            para = new SqlParameter("@Email", SqlDbType.NVarChar);
            para.Value = ngDTO.Email;
            comand.Parameters.Add(para);
            para = new SqlParameter("@DiaChi", SqlDbType.NVarChar);
            para.Value = ngDTO.DiaChi;
            comand.Parameters.Add(para);
            para = new SqlParameter("@GioiTinh", SqlDbType.NVarChar);
            para.Value = ngDTO.GioiTinh;
            comand.Parameters.Add(para);
            para = new SqlParameter("@DienThoai", SqlDbType.NVarChar);
            para.Value = ngDTO.DienThoai;
            comand.Parameters.Add(para);*/
            string sql = string.Format("UPDATE NguoiDung SET HoTen='{0}',NgaySinh='{1}',Email='{2}',DiaChi='{3}',GioiTinh='{4}',DienThoai='{5}' WHERE TenDangNhap = '{6}'", ngDTO.HoTen, ngDTO.NgaySinh, ngDTO.Email, ngDTO.DiaChi, ngDTO.GioiTinh, ngDTO.DienThoai, ngDTO.TenDangNhap);
            SqlCommand comand = new SqlCommand(sql, cnn);
            int kq;
            kq = Convert.ToInt32(comand.ExecuteScalar());
            return kq;
        }
    }
}
